package DAO;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import DBConn.DBConn;
import MySQL.Purchase;

public class PurchaseImpl implements PurchaseDao{
	//增加
	public void add(Purchase p) {
		String ssql = "insert into purchase values(?,?,?,?)";
		Connection conn = DBConn.open();
		try {
			PreparedStatement pstmt = conn.prepareStatement(ssql);
			pstmt.setString(1,p.getId());
			pstmt.setInt(2,p.getNumber());
			pstmt.setInt(3,p.getPrice());
			pstmt.setDate(4,p.getDate());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBConn.close(conn);
		}
	}
	//删除
	public void delete(String id) {
		String ssql = "delete from purchase where id=?";
		Connection conn = DBConn.open();
		try {
			PreparedStatement pstmt = conn.prepareStatement(ssql);
			pstmt.setString(1,id);
			pstmt.executeUpdate();
		} catch (SQLException e){
			e.printStackTrace();
		}finally{
			DBConn.close(conn);
		}
	}
	//通过id得到商品
	public Purchase getPurchaseById(String id) {
		String ssql = "select * from purchase where id=?";
		Connection conn = DBConn.open();
		try {
			PreparedStatement pstmt = conn.prepareStatement(ssql);
			pstmt.setString(1, id);
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()){
				int number = rs.getInt(2);
				int price = rs.getInt(3);
				Date date = rs.getDate(4);
				Purchase p = new Purchase();
				p.setId(id);
				p.setNumber(number);
				p.setPrice(price);
				p.setDate(date);
				return p;
			}
		} catch (SQLException e){
			e.printStackTrace();
		}finally{
			DBConn.close(conn);
		}	
		return null;
	}
	//查询
	public String query() {
		String ssql = "select * from purchase";
		Connection conn = DBConn.open();
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(ssql);
			String s ="";
			while(rs.next()){
				String id = rs.getString(1);
				int number = rs.getInt(2);
				int price = rs.getInt(3);
				Date date = rs.getDate(4);
				s = s+id+"   "+number+"   "+price+"   "+date+"\n";
			}
				return s;
		}catch (SQLException e1){
			e1.printStackTrace();
		}finally{
			DBConn.close(conn);
		}
		return null;
	}
		
}
